1 Imports System.Data.SqlClient
2 Imports Excel = Microsoft.Office.Interop.Excel
3
4 Public Class frmLogs
5
6
7 Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
8 Me.Close()
9 End Sub
10 Sub fillCombo()
11 Try
12 Dim CN As New SqlConnection(cs)
13 CN.Open()
14 adp = New SqlDataAdapter()
15 adp.SelectCommand = New SqlCommand("SELECT distinct RTRIM(UserID) FROM Registration", CN)
16 ds = New DataSet("ds")
17 adp.Fill(ds)
18 Dim dtable As DataTable = ds.Tables(0)
19 cmbUserID.Items.Clear()
20 For Each drow As DataRow In dtable.Rows
21 cmbUserID.Items.Add(drow(0).ToString())
22 Next
23
24 Catch ex As Exception
25 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
26 End Try
27 End Sub
28
29 Private Sub cmbUserID_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbUserID.SelectedIndexChanged
30 Try
31 con = New SqlConnection(cs)
32 con.Open()
33 cmd = New SqlCommand("SELECT RTRIM(UserID),Date,RTRIM(Operation) from Logs where UserID=@d1 order by date", con)
34 cmd.Parameters.AddWithValue("@d1", cmbUserID.Text)
35 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
36 dgw.Rows.Clear()
37 While (rdr.Read() = True)
38 dgw.Rows.Add(rdr(0), rdr(1), rdr(2))
39 End While
40 con.Close()
41 Catch ex As Exception
42 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
43 End Try
44 End Sub
45 Private Sub GetData()
46 Try
47 con = New SqlConnection(cs)
48 con.Open()
49 cmd = New SqlCommand("SELECT RTRIM(UserID),Date,RTRIM(Operation) from Logs order by Date", con)
50 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
51 dgw.Rows.Clear()
52 While (rdr.Read() = True)
53 dgw.Rows.Add(rdr(0), rdr(1), rdr(2))
54 End While
55 con.Close()
56 Catch ex As Exception
57 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
58 End Try
59 End Sub
60 Private Sub frmLogs_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
61 fillCombo()
62 GetData()
63 End Sub
64 Sub Reset()
65 cmbUserID.SelectedIndex = -1
66 dtpDateFrom.Text = Today
67 dtpDateTo.Text = Now
68 GetData()
69 fillCombo()
70 End Sub
71 Private Sub btnReset_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnReset.Click
72 Reset()
73 End Sub
74
75 Private Sub btnGetData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGetData.Click
76 Try
77 con = New SqlConnection(cs)
78 con.Open()
79 cmd = New SqlCommand("SELECT RTRIM(UserID),Date,RTRIM(Operation) from logs where Date between @date1 and @date2 order by Date", con)
80 cmd.Parameters.Add("@date1", SqlDbType.DateTime, 30, "Date").Value = dtpDateFrom.Value.Date
81 cmd.Parameters.Add("@date2", SqlDbType.DateTime, 30, "Date").Value = dtpDateTo.Value
82 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
83 dgw.Rows.Clear()
84 While (rdr.Read() = True)
85 dgw.Rows.Add(rdr(0), rdr(1), rdr(2))
86 End While
87 con.Close()
88 Catch ex As Exception
89 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
90 End Try
91 End Sub
92
93 Private Sub btnClose_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
94 Me.Close()
95 End Sub
96
97 Private Sub btnExportExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExportExcel.Click
98 Dim rowsTotal, colsTotal As Short
99 Dim I, j, iC As Short
100 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor
101 Dim xlApp As New Excel.Application
102 Try
103 Dim excelBook As Excel.Workbook = xlApp.Workbooks.Add
104 Dim excelWorksheet As Excel.Worksheet = CType(excelBook.Worksheets(1), Excel.Worksheet)
105 xlApp.Visible = True
106
107 rowsTotal = dgw.RowCount
108 colsTotal = dgw.Columns.Count - 1
109 With excelWorksheet
110 .Cells.Select()
111 .Cells.Delete()
112 For iC = 0 To colsTotal
113 .Cells(1, iC + 1).Value = dgw.Columns(iC).HeaderText
114 Next
115 For I = 0 To rowsTotal - 1
116 For j = 0 To colsTotal
117 .Cells(I + 2, j + 1).value = dgw.Rows(I).Cells(j).Value
118 Next j
119 Next I
120 .Rows("1:1").Font.FontStyle = "Bold"
121 .Rows("1:1").Font.Size = 12
122
123 .Cells.Columns.AutoFit()
124 .Cells.Select()
125 .Cells.EntireColumn.AutoFit()
126 .Cells(1, 1).Select()
127 End With
128 Catch ex As Exception
129 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
130 Finally
131 'RELEASE ALLOACTED RESOURCES
132 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default
133 xlApp = Nothing
134 End Try
135 End Sub
136
137 Private Sub dgw_RowPostPaint(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowPostPaintEventArgs) Handles dgw.RowPostPaint
138 Dim strRowNumber As String = (e.RowIndex + 1).ToString()
139 Dim size As SizeF = e.Graphics.MeasureString(strRowNumber, Me.Font)
140 If dgw.RowHeadersWidth < Convert.ToInt32((size.Width + 20)) Then
141 dgw.RowHeadersWidth = Convert.ToInt32((size.Width + 20))
142 End If
143 Dim b As Brush = SystemBrushes.ControlText
144 e.Graphics.DrawString(strRowNumber, Me.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2))
145
146 End Sub
147 Public Sub DeleteRecord()
148 Try
149 Dim RowsAffected As Integer = 0
150 con.Open()
151 Dim ct As String = "delete from logs"
152 cmd = New SqlCommand(ct)
153 cmd.Connection = con
154 RowsAffected = cmd.ExecuteNonQuery()
155 If RowsAffected > 0 Then
156 con = New SqlConnection(cs)
157 con.Open()
158 Dim st As String = "deleted the all logs till date '" & Now & "'"
159 LogFunc(lblUser.Text, st)
160 MessageBox.Show("Successfully deleted", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
161 Reset()
162 Reset()
163 GetData()
164 Else
165 MessageBox.Show("No record found", "Sorry", MessageBoxButtons.OK, MessageBoxIcon.Information)
166 Reset()
167 End If
168 con.Close()
169 Catch ex As Exception
170 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
171 End Try
172 End Sub
173 Private Sub btnDeleteAllLogs_Click(sender As System.Object, e As System.EventArgs) Handles btnDeleteAllLogs.Click
174 Try
175 If MessageBox.Show("Do you really want to delete all logs?", "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) = Windows.Forms.DialogResult.Yes Then
176 DeleteRecord()
177 End If
178 Catch ex As Exception
179 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
180 End Try
181 End Sub
182
183 Private Sub cmbUserID_Format(sender As System.Object, e As System.Windows.Forms.ListControlConvertEventArgs) Handles cmbUserID.Format
184 If (e.DesiredType Is GetType(String)) Then
185 e.Value = e.Value.ToString.Trim
186 End If
187 End Sub
188 End Class
